﻿
USE master
if exists (select * from sys.databases where name='NhaSach1042085')
	drop database NhaSach1042085
create database NhaSach1042085
go
USE NhaSach1042085
go
CREATE TABLE Sach
(
MaSach INT IDENTITY NOT NULL,
TenSach NVARCHAR(255),
MaTheLoai INT,
MaTacGia INT,
SoLuong INT,
DonGia float,
TinhTrang INT DEFAULT 1, 
 PRIMARY KEY(MaSach)
)
GO
CREATE TABLE TheLoai
(
MaTheLoai INT IDENTITY NOT NULL ,
TenTheLoai NVARCHAR(255),
TinhTrang INT DEFAULT 1,
PRIMARY KEY(MaTheLoai)
)
Go
CREATE TABLE TacGia
(
MaTacGia INT IDENTITY NOT NULL ,
TenTacGia NVARCHAR(255),
TinhTrang INT DEFAULT 1,
PRIMARY KEY(MaTacGia)
)
GO
CREATE TABLE PhieuNhap
(
MaPhieuNhap INT IDENTITY ,
NgayNhap DATETIME,
TinhTrang INT DEFAULT 1,
PRIMARY KEY(MaPhieuNhap)
)
GO
CREATE TABLE ChiTietPhieuNhap
(
MaChiTiet INT IDENTITY ,
MaPhieuNhap INT,
MaSach INT,
SoLuong INT,
PRIMARY KEY(MaChiTiet)
)
GO
CREATE TABLE HoaDon
(
MaHoaDon INT IDENTITY ,
MaKhachHang INT,
NgayLap DATETIME,
TinhTrang INT DEFAULT 1,
PRIMARY KEY(MaHoaDon)
)
GO
CREATE TABLE ChiTietHoaDon
(
MaChiTiet INT IDENTITY ,
MaHoaDon INT,
MaSach INT,
DonGiaBan FLOAT,
PRIMARY KEY(MaChiTiet)
)
GO
CREATE TABLE KhachHang
(
MaKhachHang INT IDENTITY ,
HoTen NVARCHAR(255),
DiaChi NVARCHAR(255),
DienThoai varchar(15),
Email varchar(255),
TienNo FLOAT,
TinhTrang INT DEFAULT 1,
PRIMARY KEY(MaKhachHang)
)
GO
CREATE TABLE PhieuThuTien
(
MaPhieuThu INT IDENTITY ,
MaKhachHang INT,
NgayThu DATETIME,
SoTienThu FLOAT,
TinhTrang INT DEFAULT 1,
PRIMARY KEY(MaPhieuThu)
)
GO

CREATE TABLE BaoCaoCongNo
(
MaBaoCaoCongNo INT IDENTITY,
Thang DATETIME,
TinhTrang INT DEFAULT 1,
PRIMARY KEY(MaBaoCaoCongNo)
)
CREATE TABLE ChiTietBaoCaoCongNo
(
MaChiTiet INT IDENTITY,
MaKhachHang INT,
MaBaoCaoCongNo INT,
NoDau FLOAT,
PhatSinh FLOAT,
NoCuoi FLOAT,
PRIMARY KEY(MaChiTiet)
)
Go
CREATE TABLE BaoCaoTon
(
MaBaoCaoTon INT IDENTITY,
Thang DATETIME,
TinhTrang INT DEFAULT 1,
PRIMARY KEY(MaBaoCaoTon)
)
Go
CREATE TABLE ChiTietBaoCaoTon
(
MaChiTiet INT IDENTITY,
MaBaoCaoTon INT,
MaSach INT,
TonDau INT,
PhatSinh INT, 
TonCuoi INT,
PRIMARY KEY(MaChiTiet)
)
Go
CREATE TABLE ThamSo
(
MaThamSo INT IDENTITY,
TenThamSo NVARCHAR(255),
GiaTri FLOAT,
SuDung INT DEFAULT 1,
QuiDinh INT,
PRIMARY KEY(MaThamSo)
)
Go
CREATE TABLE NguoiDung
(
TenDangNhap varchar(50),
MatKhau varchar(50),
HoTen NVARCHAR(255),
MaChucVu INT,
TinhTrang INT DEFAULT 1,
PRIMARY KEY(TenDangNhap)
)

CREATE TABLE ChucVu
(
MaChucVu INT IDENTITY,
TenChucVu NVARCHAR(255),
Quyen INT,
TinhTrang INT,
PRIMARY KEY(MaChucVu)
)

ALTER TABLE Sach
	ADD 
		CONSTRAINT FK_Sach_TheLoai FOREIGN KEY(MaTheLoai) REFERENCES TheLoai(MaTheLoai),
		CONSTRAINT FK_Sach_TacGia FOREIGN KEY(MaTacGia) REFERENCES TacGia(MaTacGia)
ALTER TABLE ChiTietPhieuNhap
	ADD
		CONSTRAINT FK_ChiTietPhieuNhap_Sach FOREIGN KEY(MaSach) REFERENCES Sach(MaSach),
		CONSTRAINT FK_ChiTietPhieuNhap_PhieuNhapSach FOREIGN KEY(MaPhieuNhap) REFERENCES PhieuNhap(MaPhieuNhap)
ALTER TABLE ChiTietHoaDon
	ADD
		CONSTRAINT FK_ChiTietHoaDon_HoaDon FOREIGN KEY(MaHoaDon) REFERENCES HoaDon(MaHoaDon),
		CONSTRAINT FK_ChiTietHoaDon_Sach FOREIGN KEY(MaSach) REFERENCES Sach(MaSach)

ALTER TABLE HoaDon
	ADD
		CONSTRAINT FK_HoaDon_KhachHang FOREIGN KEY(MaKhachHang) REFERENCES KhachHang(MaKhachHang)

ALTER TABLE PhieuThuTien
	ADD
		CONSTRAINT FK_PhieuThuTien_KhachHang FOREIGN KEY(MaKhachHang) REFERENCES KhachHang(MaKhachHang)

ALTER TABLE NguoiDung
	ADD
		CONSTRAINT FK_NguoiDung_ChucVu FOREIGN KEY(MaChucVu) REFERENCES ChucVu(MaChucVu)

ALTER TABLE ChiTietBaoCaoTon
	ADD
		CONSTRAINT FK_ChiTietTon_BaoCaoTon FOREIGN KEY(MaBaoCaoTon) references BaoCaoTon(MaBaoCaoTon),
		CONSTRAINT FK_ChiTietTon_Sach FOREIGN KEY(MaSach) references Sach(MaSach)
ALTER TABLE ChiTietBaoCaoCongNo
	ADD
		CONSTRAINT FK_ChiTietCongNo_CongNo FOREIGN KEY(MaBaoCaoCongNo) references BaoCaoCongNo(MaBaoCaoCongNO),
		CONSTRAINT FK_ChiTietCongNo_KhachHang FOREIGN KEY(MaKhachHang) references KhachHang(MaKhachHang)




-- Tao Trigger cho cac bang dam bao khi update cot tinh trang
if exists (select * from SysObjects where name='tg_TheLoai')
	drop trigger tg_TheLoai
Go
create trigger tg_TheLoai on TheLoai
for update 
as
if update(TinhTrang)
	if exists(select * from inserted i, Sach s
				where i.TinhTrang=0 AND i.MaTheLoai=s.MaTheLoai)
	begin
		raiserror(N'Không thể Update',15,1)
		rollback tran
	end
go

--Proc lấy mã cuối cùng của 1 bảng
if exists (select * from SysObjects where NAME='spGetCurrentID')
	drop proc spGetCurrentID
go
create Proc spGetCurrentID @Table varchar(100)
as
begin	
	declare @CurrentID int
	select @CurrentID=IDENT_CURRENT(@Table)
	select return @CurrentID
end
--Nhập liệu
--TheLoai
insert into TheLoai(TenTheLoai) values(N'Văn học')
insert into TheLoai(TenTheLoai) values(N'Âm Nhạc')
insert into TheLoai(TenTheLoai) values(N'Tin học')
insert into TheLoai(TenTheLoai) values(N'Luật')
insert into TheLoai(TenTheLoai) values(N'Tin học')
insert into TheLoai(TenTheLoai) values(N'Ngoại ngữ')
insert into TheLoai(TenTheLoai) values(N'Thể thao - Du lịch')

--TacGia

insert into TacGia(TenTacGia) values(N'Nathan Bennett')
insert into TacGia(TenTacGia) values(N'Ahsaf Ladii')
insert into TacGia(TenTacGia) values(N'David Smith')
insert into TacGia(TenTacGia) values(N'Nguyễn Tân Bình')
insert into TacGia(TenTacGia) values(N'Đỗ Thị Hồng Vân')
insert into TacGia(TenTacGia) values(N'Nick Frank')
insert into TacGia(TenTacGia) values(N'Esmar')
insert into TacGia(TenTacGia) values(N'Lê Minh')

--Sach
insert into Sach(TenSach,MaTheLoai,MaTacGia,SoLuong,DonGia) values(N'Cẩm nang giám đốc',1,1,400,49000)
insert into Sach(TenSach,MaTheLoai,MaTacGia,SoLuong,DonGia) values(N'Thế giới cong ',1 ,2 , 200,50000 )
insert into Sach(TenSach,MaTheLoai,MaTacGia,SoLuong,DonGia) values(N'Marketing 3.0 ', 1, 3,300 ,14000 )
insert into Sach(TenSach,MaTheLoai,MaTacGia,SoLuong,DonGia) values(N'Tinh Thần Kinh Doanh và Sự khởi nghiệp ',3 ,2 ,500 ,150000 )
insert into Sach(TenSach,MaTheLoai,MaTacGia,SoLuong,DonGia) values(N'Kế Toán Tài Chính',3 ,2 , 3,80000 )
insert into Sach(TenSach,MaTheLoai,MaTacGia,SoLuong,DonGia) values(N'Quản Trị Cung Ứng', 3,4 ,300 ,230000 )
--ThamSo
insert into ThamSo(TenThamSo,GiaTri,SuDung,QuiDinh) values(N'Lượng Nhập tối thiểu',150,1,1);
insert into ThamSo(TenThamSo,GiaTri,SuDung,QuiDinh) values(N'Lượng Tồn tối đa',300,1,1);


